package com.grasscove.namethat.adapters;

import com.grasscove.namethat.models.Word;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class WordsDbAdapter {
	private static final int DATABASE_VERSION = 1;

	public static final String DATABASE_NAME = "NameThat.db";
	public static final String DATABASE_TABLE = "wordlist";

	public static final String KEY_ID = "_id";
	public static final int ID_COLUMN = 0;

	public static final String KEY_CID = "cid";
	public static final int CID_COLUMN = 1;

	public static final String KEY_WORD = "word";
	public static final int WORD_COLUMN = 2;

	private SQLiteDatabase mDb;
	private WordsDbHelper mDbHelper;

	private final Context mContext;

	private static class WordsDbHelper extends SQLiteOpenHelper
	{
		// SQL Statement to create a new database.
		private static final String DATABASE_CREATE =
			"CREATE TABLE "+ DATABASE_TABLE +"("
				+ KEY_ID +" integer primary key autoincrement,"
				+ KEY_CID +" integer not null,"
				+ KEY_WORD +" varchar(128) not null"
			+");";

		public WordsDbHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		/**
		 * Called when no database exists in disk and the helper class needs to create a new one.
		 */
		@Override
		public void onCreate(SQLiteDatabase db)
		{
			Log.v("WordlistDbHelper.onCreate()", DATABASE_CREATE);
			db.execSQL(DATABASE_CREATE);
		}

		/**
		 *  Called when there is a database version mismatch meaning that the version of the
		 *  database on disk needs to be upgraded to the current version.
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			// Log the version upgrade.
			Log.v("WordlistDbHelper.onUpgrade()", "Upgrading from version " + oldVersion
					+ " to " + newVersion
					+ ", which will destroy all old data");

			/**
			 *  Upgrade the existing database to conform to the new version.
			 *  Multiple previous versions can be handled by comparing _oldVersion and _newVersion values.
			 *  The simplest case is to drop the old table and create a new one.
			 */
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

			// Create a new one.
			onCreate(db);
		}
	}

	public WordsDbAdapter(Context context)
	{
		mContext = context;
		mDbHelper = new WordsDbHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
	}

	public WordsDbAdapter open() throws SQLException {
		try {
			mDb = mDbHelper.getWritableDatabase();
		} catch(SQLiteException e) {
			mDb = mDbHelper.getReadableDatabase();
		}
		return this;
	}

	public void close() {
		mDb.close();
	}

	public long insert(Word word)
	{
		ContentValues contentValues = new ContentValues();

		contentValues.put(KEY_CID, word.getCategoryId());
		contentValues.put(KEY_WORD, word.getWord());

		return mDb.insert(DATABASE_TABLE, null, contentValues);
	}

	public int delete(int id) {
		return mDb.delete(DATABASE_TABLE, KEY_ID + "=" + id, null);
	}

	public Cursor getAll() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_CID, KEY_WORD }, null, null, null, null, null);
	}

	public Cursor getRandom() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, null, null, null, null, "RANDOM() LIMIT 1");
	}

	public Cursor getRandomFromCategory(int categoryId) {
		if(categoryId != 0) {
			String where = KEY_CID +" = "+ categoryId;
			return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, where, null, null, null, "RANDOM() LIMIT 1");
		} else {
			return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, null, null, null, null, "RANDOM() LIMIT 1");
		}
	}

	public Cursor getWordCountFromCategory(int categoryId) {
		if(categoryId != 0) {
			//String where = KEY_CID +" = "+ categoryId;
			return mDb.rawQuery("SELECT COUNT(*) FROM categories WHERE cid = "+ (int)categoryId, new String[] { KEY_ID, KEY_WORD });
			//return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, where, null, null, null, "RANDOM() LIMIT 1");
		} else {
			//return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, null, null, null, null, "RANDOM() LIMIT 1");
			return mDb.rawQuery("SELECT COUNT(*) FROM categories", new String[] { KEY_ID, KEY_WORD });
		}
	}

	public Cursor getAllFromCategory(int categoryId) {
		if(categoryId != 0) {
			String where = KEY_CID +" = "+ categoryId;
			return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, where, null, null, null, KEY_WORD);
		} else {
			return mDb.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_WORD }, null, null, null, null, KEY_WORD);
		}
	}

	public void execSql(String sql) {
		mDb.execSQL(sql);
	}

	public Word getRowByWord(String word)
	{
		Word mWord = new Word();

		String where = KEY_WORD +" = '"+ word +"'";
		Cursor c = mDb.query(DATABASE_TABLE, null, where, null, null, null, null);
		c.moveToFirst();

		mWord.setNew(false);
		mWord.setId(c.getInt(ID_COLUMN));
		mWord.setCategoryId(c.getInt(CID_COLUMN));
		mWord.setWord(c.getString(WORD_COLUMN));

//Log.v("getRow().1", Integer.toString(c.getInt(DBM_COLUMN)));

		c.close();
		return mWord;
	}

	public Word getRow(int id)
	{
		Word word = new Word();

		String where = KEY_ID +" = "+ id;
		Cursor c = mDb.query(DATABASE_TABLE, null, where, null, null, null, null);
		c.moveToFirst();

		word.setNew(false);
		word.setId(c.getInt(ID_COLUMN));
		word.setCategoryId(c.getInt(CID_COLUMN));
		word.setWord(c.getString(WORD_COLUMN));

//Log.v("getRow().1", Integer.toString(c.getInt(DBM_COLUMN)));

		return word;
	}

	public int update(Word word) {
		String where = KEY_ID + "=" + word.getId();

		ContentValues contentValues = new ContentValues();

		contentValues.put(KEY_ID, word.getId());
		contentValues.put(KEY_CID, word.getCategoryId());
		contentValues.put(KEY_WORD, word.getWord());

		return mDb.update(DATABASE_TABLE, contentValues, where, null);
	}
}